package com.dao;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.log4j.Logger;

import com.config.CONFIG;
import com.dbtools.ConnectionTools;
import com.dbtools.Dispose;
import com.dbtools.PageableResultSet2;
import com.exception.SelectException;
import com.exception.SystemErrorException;

/**
 * @author luanzhe ��ݿ����Ļ��� TODO To change the template for this generated
 *         type comment go to Window - Preferences - Java - Code Style - Code
 *         Templates
 */
public class DataOperations {
	private static Logger log = Logger.getLogger(DataOperations.class);

	// private DataSource dataSource = null;
	protected Connection connection = null;

	private List sqlLogList = new ArrayList();// ��¼����sql

	/** ��ҳ�� */
	private int totalPage = 0;

	/** �ܼ�¼�� */
	private int totalData = 0;

	/** ��ǰҳ */
	private int currentPage = 0;

	/** ÿҳ��¼�� */
	private int pageSize = 0;

	public DataOperations() {
		this.connection = this.getConnection();
	}

	public DataOperations(Connection connection) {
		this.connection = connection;
	}

	/**
	 * �������Stream ת���� String
	 * 
	 * @param InputStream
	 * @return String
	 * @throws SystemErrorException
	 */
	public String stream2String(InputStream stream) throws SystemErrorException {
		if (stream == null)
			return "";
		ObjectInputStream objectstream = null;
		try {
			objectstream = new ObjectInputStream(stream);
			String tmpString = (String) objectstream.readObject();
			return tmpString;
		} catch (IOException e) {
			throw new SystemErrorException("IOException throwed from"
					+ this.getClass().getName(), e);
		} catch (ClassNotFoundException e) {
			throw new SystemErrorException("ClassNotException throwed from"
					+ this.getClass().getName(), e);
		} finally {
			try {
				objectstream.close();
			} catch (IOException e1) {
				throw new SystemErrorException("IOException throwed from"
						+ this.getClass().getName(), e1);
			}
		}
	}

	/**
	 * �������String ת���� Stream
	 * 
	 * @param String
	 * @return ByteArrayOutputStream
	 * @throws SystemErrorException
	 */
	public ByteArrayOutputStream string2Stream(String string)
			throws SystemErrorException {
		ByteArrayOutputStream bytearrayoutputstream = new ByteArrayOutputStream();
		ObjectOutputStream objectoutputstream;
		try {
			objectoutputstream = new ObjectOutputStream(bytearrayoutputstream);
			objectoutputstream.writeObject(string);
			objectoutputstream.close();
			return bytearrayoutputstream;
		} catch (IOException e) {

			throw new SystemErrorException("IOException throwed from"
					+ this.getClass().getName(), e);
		}
	}

	public String clob2String(Clob clob) {
		if (clob == null)
			return "";
		StringBuffer content = new StringBuffer();
		try {
			Reader reader = clob.getCharacterStream();
			BufferedReader bf = new BufferedReader(reader);
			String tmp = "";
			while ((tmp = bf.readLine()) != null) {
				content.append(tmp);
			}
			bf.close();
			reader.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {

		}
		return content.toString();
	}

	public void writeClob(String tableName, String col1, String content,
			String col2, String key) throws SQLException {
		String sqlSelect = "select " + col1 + " from " + tableName + " where "
				+ col2 + " = '" + key + "'";
		String sqlUpdate = "update " + tableName + " set " + col1
				+ " = ? where " + col2 + " = ?";

		ResultSet rest = this.executeQuery(sqlSelect);
		PreparedStatement pstmt = null;
		try {
			CLOB clob = null;
			while (rest.next()) {
				clob = (CLOB) rest.getClob(col1);
			}
			pstmt = this.connection.prepareStatement(sqlUpdate);

			// clob.putString(1,content);

			pstmt.setClob(1, clob);
			pstmt.setString(2, key);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null)
				pstmt.close();
			Dispose.dispose(rest);
		}
	}

	/**
	 * ��ݿ��ѯ
	 * 
	 * @param sql
	 * @return
	 * @throws SelectException
	 */
	public List getDataFromDatabase(String sql) throws SelectException {
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;

		try {
			rest = this.executeQuery(sql);
			ResultSetMetaData rsmd = rest.getMetaData();
			int count = rsmd.getColumnCount();
			while (rest.next()) {
				map = new HashMap();
				for (int i = 1; i <= count; i++) {
					map.put(rsmd.getColumnName(i),
							rest.getString(i) == null ? "" : rest.getString(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/**
	 * ��ݿ��ѯ
	 * 
	 * @param sql
	 * @return
	 * @throws SelectException
	 */
	public List getDataFromDatabase(String sql, String currentPage)
			throws SelectException {
		List list = new ArrayList();
		Map map = null;
		PageableResultSet2 rest = null;

		try {
			rest = this.executePageableQuery(sql, currentPage);
			ResultSetMetaData rsmd = rest.getMetaData();
			int count = rsmd.getColumnCount();
			for (int i = 0; i < rest.getPageRowsCount(); i++) {
				map = new HashMap();
				for (int j = 1; j <= count; j++) {
					map.put(rsmd.getColumnName(j),
							rest.getObject(j) == null ? "" : rest.getObject(j));
				}
				list.add(map);
				rest.next();
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/** ȡ�����е�ֵ */
	public String getNextValOfSeq(String seqName) throws SelectException {
		String value = "";
		String sql = "select " + seqName + ".nextval from dual";

		ResultSet rest = null;
		try {
			rest = this.executeQuery(sql);
			while (rest.next()) {
				value = rest.getString(1);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return value;
	}

	public int queryCount(String sql) throws SQLException {
		ResultSet rest = null;
		int count = 0;
		try {
			rest = this.executeQuery(sql);
			while (rest.next()) {
				count = rest.getInt(1);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			Dispose.dispose(rest);
		}
		return count;
	}

	/**
	 * �����Զ��ύ
	 * 
	 * @param autoCommit
	 * @return
	 */
	public boolean setAutoCommit(boolean autoCommit) {
		boolean flag = true;
		try {
			this.connection.setAutoCommit(autoCommit);
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}

	/**
	 * �ύ
	 * 
	 * @return
	 */
	public boolean commit() {
		boolean flag = true;
		try {
			this.connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}

	/**
	 * �ع�
	 * 
	 * @return
	 */
	public boolean rollback() {
		boolean flag = true;
		try {
			this.connection.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}
		return flag;
	}

	/**
	 * ִ��Ԥ��������
	 * 
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql, String[] values)
			throws SQLException {
		ResultSet rest = null;
		try {
			PreparedStatement pStatm = this.connection.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < values.length; i++) {
				pStatm.setString(i + 1, values[i]);
			}
			rest = pStatm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
		}
		return rest;
	}

	/**
	 * ִ�в�ѯ����
	 * 
	 * @throws SQLException
	 */
	protected ResultSet executeQuery(String sql) throws SQLException {
		ResultSet rest = null;
		try {
			Statement stmt = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rest = stmt.executeQuery(sql);
		} catch (SQLException e) {
			log.error("sql:" + sql + "\n" + e.getMessage());
			throw e;
		} finally {
		}
		return rest;
	}

	/**
	 * ִ�в�ѯ����
	 * 
	 * @throws SQLException
	 */
	protected PageableResultSet2 executePageableQuery(String sql,
			String currentPage) throws SQLException {
		PageableResultSet2 rest = null;
		int pageNum = 1;
		try {
			pageNum = Integer.parseInt(currentPage);
		} catch (NumberFormatException e) {

		}
		try {
			Statement stmt = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rest = new PageableResultSet2(stmt.executeQuery(sql));
			rest.setPageSize(CONFIG.ITEM_COUNT_PER_PAGE);
			rest.gotoPage(pageNum);
			this.currentPage = rest.getCurPage();
			this.totalPage = rest.getPageCount();
			this.pageSize = rest.getPageSize();
			this.totalData = rest.getRowsCount();
		} catch (SQLException e) {
			log.error("sql:" + sql + "\n" + e.getMessage());
			// e.printStackTrace();
			throw e;
		} finally {
		}
		return rest;
	}

	/**
	 * ִ��delete/update/insert����
	 */
	public boolean executeUpdate(String sql) throws SQLException {
		Statement stmt = null;
		boolean flag = true;
		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			sqlLogList.add(sql);// ��¼sql���

		} catch (SQLException e) {
			flag = false;
			log.error("sql:" + sql + "\n" + e.getMessage());
			// e.printStackTrace();
			throw e;
		} finally {
			stmt.close();
		}
		return flag;
	}

	public boolean executeUpdate(String sql, boolean isCommit)
			throws SQLException {
		Statement stmt = null;
		boolean flag = true;
		connection.setAutoCommit(false);
		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			sqlLogList.add(sql);// ��¼sql���
			if (isCommit)
				connection.commit();
		} catch (SQLException e) {
			connection.rollback();
			log.error("errer sql:" + sql + "\n" + e.getMessage());
			// e.printStackTrace();
			flag = false;
			throw e;
		} finally {
			stmt.close();
			if (isCommit)
				connection.setAutoCommit(true);
		}
		return flag;
	}

	/**
	 * ִ����insert����
	 * 
	 * @param sqlList
	 * @return
	 * @throws SQLException
	 * @throws ArpReportException
	 */
	public boolean executeUpdate(List sqlList, boolean isCommit)
			throws SQLException {
		Statement stmt = null;
		boolean flag = true;
		String sql = "";
		Iterator iterator = sqlList.iterator();
		connection.setAutoCommit(false);
		try {
			stmt = connection.createStatement();
			while (iterator.hasNext()) {
				sql = iterator.next().toString();
				stmt.executeUpdate(sql);
			}
			sqlLogList.addAll(sqlList);// ��¼sql���

			if (isCommit)
				connection.commit();
		} catch (SQLException e) {
			connection.rollback();
			log.error("errer sql:" + sql + "\n" + e.getMessage());
			// e.printStackTrace();
			flag = false;
			throw e;
		} finally {
			stmt.close();
			if (isCommit)
				connection.setAutoCommit(true);
		}
		return flag;
	}

	/**
	 * �ر���ݿ�l��
	 * 
	 * @return boolean
	 */
	public boolean disConnect() {
		try {
			sqlLogList = null;
			if (!(this.connection == null || this.connection.isClosed())) {
				this.connection.close();
				log.info("release the connection! " + this.connection);
				this.connection = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * @return Returns the curPage ��ǰҳ.
	 */
	public int getCurrentPage() {
		return currentPage;
	}

	/**
	 * @return Returns the pageCount ��ҳ��.
	 */
	public int getTotalPage() {
		return totalPage;
	}

	/**
	 * @return Returns the pageSize ÿҳ��¼��.
	 */
	public int getPageSize() {
		return pageSize;
	}

	/**
	 * @return Returns the rowsCount �ܼ�¼��.
	 */
	public int getTotalData() {
		return totalData;
	}

	/**
	 * �ر�statment
	 * 
	 * @param selectSQL
	 *            The selectSQL to set.
	 */
	public void close(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
			}
			stmt = null;
		}
	}

	/**
	 * @return sqlLogList
	 */
	public List getSqlLogList() {
		return sqlLogList;
	}

	/**
	 * �ر�ResultSet
	 * 
	 * @param selectSQL
	 *            The selectSQL to set.
	 */
	public void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			rs = null;
		}
	}

	/**
	 * �ر�PreparedStatement
	 * 
	 * @param selectSQL
	 *            The selectSQL to set.
	 */
	public void close(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
			}
			pstmt = null;
		}
	}

	/**
	 * @return Returns the connection.
	 */
	public Connection getConnection() {
		try {
			if (this.connection == null || this.connection.isClosed()) {
				try {
					this.connection = ConnectionTools.getConnection();
				} catch (Exception e) {
					try {
						this.connection = ConnectionTools.getConnection2();
					} catch (Exception e1) {
						e1.printStackTrace();
					}
				}
				log.info("get a connection! " + this.connection);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.connection;
	}

	/**
	 * @param connection
	 *            The connection to set.
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	/**
	 * @�ж���ݿ����Ƿ����ĳ�ű�
	 * @author wangk071226
	 * @����: catalog:Ŀ¼��ƣ�һ�㶼Ϊ��
	 * 
	 * @����schema:��ݿ������oracle4˵���û���
	 * @����tablename:�����
	 * 
	 * @����type :�������(TABLE | VIEW)
	 * @ע�⣺��ʹ�ù���У�������Ʊ���ʹ�ô�д�ġ�
	 */

	public boolean DatabaseMetaData(String catalog, String schemaPattern,
			String tableNamePattern, String[] types) {
		boolean flag = false;
		try {
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet rsTables = meta.getTables(catalog, "ZSY02",
					tableNamePattern, types);

			if (rsTables.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * @�ж���ݿ��е�ĳ�ű��Ƿ���ĳ���ֶ�
	 * 
	 * @author wangk071226
	 * @����: catalog:������
	 * @����schema:��ݿ������oracle4˵���û���
	 * @����tablename:�����
	 * 
	 * @����type :�����ģʽ
	 * 
	 * @����: COLUMN_NAME�����ֶε�����
	 * @����: TYPE_NAME�����������
	 * 
	 * @����: "%"��ʾ��������ģ��ֶΣ���
	 * 
	 * @ע�⣺��ʹ�ù���У�������Ʊ���ʹ�ô�д�ġ�
	 */
	/** ����δ���� */

	public List DatabaseMetaDataType(String catalog, String schemaPattern,
			String tableNamePattern, String[] types) {
		List list = new ArrayList();
		try {
			DatabaseMetaData meta = connection.getMetaData();

			String columnName;
			// String columnType;
			ResultSet colRet = meta
					.getColumns(null, "%", tableNamePattern, "%");
			while (colRet.next()) {
				columnName = colRet.getString("COLUMN_NAME");
				// columnType = colRet.getString("TYPE_NAME");
				list.add(columnName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
